<?php

	// creates or/and open the database
	$handler = sqlite_open("../database/timemanagement.db", 0666, $error);
	
	// exception handling
	if(!$handler) die($error);
	
	echo "Create Database successful!";
	
	// ------------ PERSONAL --------------
	// CREATE TABLE statement
	$stm = "CREATE TABLE Personal(pk_persnr INTEGER PRIMARY KEY, name TEXT, passwort TEXT, gebdatum DATE, rolle TEXT)";
	
	// save true on success or false on failure to check variable
	$check = sqlite_exec($handler, $stm, $error);
	
	if(!$check)
		die("Cannot execute statement: $error");
	
	// ------------ PROJEKT --------------
	// CREATE TABLE statement
	$stm = "CREATE TABLE Projekt(pk_nr INTEGER PRIMARY KEY, name TEXT, deadline DATE, budget DECIMAL(14,2))";
	
	// save true on success or false on failure to check variable
	$check = sqlite_exec($handler, $stm, $error);
	
	// if false the script will stop 
	if(!$check)
		die("Cannot execute statement: $error");
		
	
	// ------------ TERMIN --------------
	// CREATE TABLE statement
	$stm = "CREATE TABLE Termin(pk_id INTEGER PRIMARY KEY, text TEXT, uhrzeit DATETIME, datum DATE, ort TEXT, fk_projekt INT,
			FOREIGN KEY (fk_projekt) REFERENCES projekt(pk_nr))";
	
	// save true on success or false on failure to check variable
	$check = sqlite_exec($handler, $stm, $error);
	
	// if false the script will stop
	if(!$check)
		die("Cannot execute statement: $error");
	
	// ------------ ARBEITSZEIT --------------
	// CREATE TABLE statement
	$stm = "CREATE TABLE Arbeitszeit(pk_id INTEGER PRIMARY KEY, von DATETIME, bis DATETIME)";
	
	// save true on success or false on failure to check variable
	$check = sqlite_exec($handler, $stm, $error);
	
	// if false the script will stop
	if(!$check)
		die("Cannot execute statement: $error");
	
	
	// ------------ ARBEITET --------------
	// CREATE TABLE statement
	$stm = "CREATE TABLE Arbeitet(pk_id INTEGER PRIMARY KEY, fk_personal INTEGER, fk_arbeitszeit INTEGER, fk_projekt INTEGER,
			FOREIGN KEY(fk_personal) REFERENCES Personal(pk_id),
			FOREIGN KEY(fk_arbeitszeit) REFERENCES Arbeitszeit(pk_id),
			FOREIGN KEY(fk_projekt) REFERENCES Projekt(pk_id))";
	
	// save true on success or false on failure to check variable
	$check = sqlite_exec($handler, $stm, $error);
	
	// if false the script will stop
	if(!$check)
		die("Cannot execute statement: $error");
	
	// close database
	sqlite_close($handler);
	